package com.model;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

public class DbFun {
	
	public static DbFun dao=new DbFun();
	
	/**
	 * 学生论文查重界面，根据学生id查询相应记录
	 * @param stuId
	 * @param type
	 * @return
	 */
	public Page<Record> select_ensurer(int pageNumber, int pageSize,int stuId)
	{
		String sql="from ensurer WHERE student_id="+stuId+"";
		Page<Record> ensurer = Db.paginate(pageNumber, pageSize, "select *", sql);;
		return ensurer;
	}
	
	/**
	 * 根据学生id与论文种类查询相应评论
	 * @param stuId
	 * @param type
	 * @return
	 */
	public String teacher_memo(int stuId, String type)
	{
		String memo="";
		String sql="select tchmemo_memo from teachermemo WHERE tchmemo_student="+stuId+" AND tchmemo_type='"+type+"'";
		memo=Db.queryStr(sql);
		return memo;
	}
	
	/**
	 * 根据学生id查找视图内容
	 * @param pageNumber
	 * @param pageSize
	 * @param stuId
	 * @return
	 */
	public Page<Record> select_stu_tch_thes(int pageNumber, int pageSize,int stuId)
	{
		String sql="from stu_tch_thes WHERE student_id="+stuId;
		Page<Record> stu_tch_thes = Db.paginate(pageNumber, pageSize, "select *", sql);;
		return stu_tch_thes;
	}
	
	/**
	 * 查询答辩组
	 * @param stuId
	 * @return
	 */
	public Map<String, Object> replygroup(int stuId)
	{
//		String sql="from apply WHERE student_id = "+stuId+"";
//		Page<Record> reply= Db.paginate(pageNumber, pageSize, "SELECT *", sql);
//		sql = "SELECT teacher.teacher_name FROM replygroup_rela_teacher "
//				+ "INNER JOIN teacher ON replygroup_rela_teacher.Replygrp_rela_tch_tch = teacher.teacher_id "
//				+ "WHERE replygroup_rela_teacher.Replygrp_rela_tch_replygrp=1";
//		List<String> tchNameList=Db.query(sql);
//		String tchName="";
//		for(int i=0 ;i<tchNameList.size();i++)
//		{
//			if(i==0)
//				tchName=tchNameList.get(i);
//			else
//				tchName=tchName+","+tchNameList.get(i);
//		}
//		Map<String, Object> m = new HashMap<String, Object>();
//		m.put("total", reply.getTotalRow());
//		m.put("rows", reply.getList());
//		m.put("tchName", tchName);
//		return m;
		String sql="select * from apply WHERE student_id = "+stuId+"";
		Record reply= Db.findFirst(sql);
		sql = "SELECT teacher.teacher_name FROM replygroup_rela_teacher "
				+ "INNER JOIN teacher ON replygroup_rela_teacher.Replygrp_rela_tch_tch = teacher.teacher_id "
				+ "WHERE replygroup_rela_teacher.Replygrp_rela_tch_replygrp=1";
		List<String> tchNameList=Db.query(sql);
		String tchName="";
		for(int i=0 ;i<tchNameList.size();i++)
		{
			if(i==0)
				tchName=tchNameList.get(i);
			else
				tchName=tchName+","+tchNameList.get(i);
		}
		Map<String, Object> m = new HashMap<String, Object>();
		m.put("Msg", reply);
		m.put("tchName", tchName);
		return m;
	}
	
	/**
	 * 查重稿url
	 * @param stuId
	 * @return
	 */
	public String ensurerUrl(int stuId)
	{
		String url="";
		String sql="SELECT thesistitle.thesistitle_ensureresulturl from thesistitle where thesistitle_id = "
				+ "(SELECT stu_rela_thesis_thesis from student_rela_thesistitle where stu_rela_thesis_stu= 2) ";
		url=Db.queryStr(sql);
		return url;
	}
	
	/**
	 * 查找学生相关信息
	 * @param stuId
	 * @return
	 */
	public Record stu_tch(int stuId)
	{
		String sql="SELECT * from stu_tch WHERE student_id="+stuId+"";
		Record stu_tch=Db.findFirst(sql);
		return stu_tch;
	}
	
	/**
	 * 学生申请答辩操作
	 * @param stuId
	 * @return
	 */
	public boolean apply(int stuId)
	{
		boolean success=false;
		Student stu=Student.dao.findById(stuId);
		if(stu.get("Student_replyconfirm1")==null || stu.get("Student_replyconfirm1")=="A")
			success=stu.set("Student_replyconfirm1", "B").update();
		return success;
	}

	
	/**
	 * 在学生老师关系表建立关联
	 * @param stuId
	 * @param tchId
	 * @return
	 */
	public boolean createTch_rela_stu(int stuId,int tchId)
	{
		boolean success=false;
		Record relate=new Record();
		relate.set("stu_rela_guidetch_stu", stuId).set("stu_rela_guidetch_tch", tchId);
		success=Db.save("student_rela_guideteacher", "stu_rela_guidetch_id", relate);
		return success;
	}
	
	/**
	 * 根据系编号查询分配结果
	 * @param page
	 * @param rows
	 * @param depId
	 * @return
	 */
	public Map<String, Object> resultTable_depId(Integer page,Integer rows,int depId)
	{
		String sql="from teacher_stuAmount WHERE teacher_department="+depId+"";
		Page<Record> result=Db.paginate(page, rows, "select *", sql);
		Map<String, Object> m = new HashMap<String, Object>();
		m.put("total", result.getTotalRow());
		m.put("rows", result.getList());
		return m;
		
	}
	
	/**
	 * 根据教师id查询对应老师的分配结果
	 * @param page
	 * @param rows
	 * @param tchId
	 * @return
	 */
	public Map<String, Object> resultTable_tchId(Integer page,Integer rows,int tchId)
	{
		String sql="from teacher_stuAmount WHERE teacher_id="+tchId+"";
		Page<Record> result=Db.paginate(page, rows, "select *", sql);
		Map<String, Object> m = new HashMap<String, Object>();
		m.put("total", result.getTotalRow());
		m.put("rows", result.getList());
		return m;
		
	}
	
	/**
	 * 根据系编号查询未分配完毕结果
	 * @param page
	 * @param rows
	 * @param depId
	 * @return
	 */
	public Map<String, Object> resultTable_no(Integer page,Integer rows,int depId)
	{
		String sql="from teacher_stuAmount WHERE teacher_department="+depId+"";
		Page<Record> result=Db.paginate(page, rows, "select *", sql);
		Map<String, Object> m = new HashMap<String, Object>();
		m.put("total", result.getTotalRow());
		m.put("rows", result.getList());
		return m;
		
	}
	
	/**
	 * 查找该系所有老师列表
	 * @param depId
	 * @return
	 */
	public List<Record> teacherList(int depId)
	{
		String sql="SELECT teacher_name , teacher_id from teacher_stuAmount where teacher_department='"+depId+"'";
		List<Record> teacherList=Db.find(sql);
		return teacherList;
	}
	
	/**
	 * 查找该系所有未分配完毕老师列表
	 * @param depId
	 * @return
	 */
	public List<Record> teacherList_no(int depId)
	{
		String sql="SELECT teacher_name , teacher_id from teacher_stuAmount where teacher_department='"+depId+"' and canchose > 0";
		List<Record> teacherList=Db.find(sql);
		return teacherList;
	}
	
	/**
	 * 查看某老师剩余的名额
	 * @param tchId
	 * @return
	 */
	public Double oddAmount(int tchId)
	{
		String sql="SELECT canchose FROM teacher_stuAmount WHERE teacher_id = '"+tchId+"'";
		Double amount = Db.queryDouble(sql);
		return amount;
	}
	
	/**
	 * 系综合设置列表
	 * @param page
	 * @param rows
	 * @param depId
	 * @return
	 */
	public Map<String, Object> departmentMessage(Integer page,Integer rows,int depId)
	{
		String sql="from department_message WHERE department_id="+depId+"";
		Page<Record> result=Db.paginate(page, rows, "select *", sql);
		Map<String, Object> m = new HashMap<String, Object>();
		m.put("total", result.getTotalRow());
		m.put("rows", result.getList());
		return m;
		
	}
	
	/**
	 * 设置某系综合信息
	 * @param depId
	 * @param tch_stu_num
	 * @param progress_month
	 * @param pass_percent
	 * @return
	 */
	public boolean setMessage(int depId,int tch_stu_num,int progress_month,int pass_percent )
	{
		boolean success = false;
		Record dep = Db.findById("department","department_id", depId).set("department_tch_stu_num", tch_stu_num)
				.set("deparment_progress_month", progress_month)
				.set("department_pass_percent", pass_percent);
		success=Db.update("department", "department_id", dep);
		return success;
	}
	
	/**
	 * 查询某系领导小组
	 * @param pageNumber
	 * @param pageSize
	 * @param department
	 * @return
	 */
	public Map<String, Object> steeringGroup(int page, int rows,int depId)
	{
		String sql="from teacher_stuAmount where teacher_category != '' and teacher_department = "+depId+"";
		Page<Record> result=Db.paginate(page, rows, "select *", sql);
		Map<String, Object> m = new HashMap<String, Object>();
		m.put("total", result.getTotalRow());
		m.put("rows", result.getList());
		return m;
	}
	
	/**
	 * 查找该系不在领导小组的教师列表
	 * @param depId
	 * @return
	 */
	public List<Record> groupTeacherList(int depId)
	{
		String sql="SELECT teacher_name , teacher_id from teacher where teacher_department='"+depId+"' and teacher_category is null";
		List<Record> teacherList=Db.find(sql);
		return teacherList;
	}
	
	/**
	 * 查询某系指导老师评阅老师分配结果
	 * @param pageNumber
	 * @param pageSize
	 * @param department
	 * @return
	 */
	public Map<String, Object> guideRelajudge(int page, int rows,int depId)
	{
		String sql="from guide_rela_judge_msg where teacher_department = "+depId+"";
		Page<Record> result=Db.paginate(page, rows, "select *", sql);
		Map<String, Object> m = new HashMap<String, Object>();
		m.put("total", result.getTotalRow());
		m.put("rows", result.getList());
		return m;
	} 
	
	/**
	 * 根据教师id查询对应老师的评阅教师分配结果
	 * @param page
	 * @param rows
	 * @param tchId
	 * @return
	 */
	public Map<String, Object> guideRelajudge_tchId(Integer page,Integer rows,int tchId)
	{
		String sql="from guide_rela_judge_msg WHERE teacher_id="+tchId+"";
		Page<Record> result=Db.paginate(page, rows, "select *", sql);
		Map<String, Object> m = new HashMap<String, Object>();
		m.put("total", result.getTotalRow());
		m.put("rows", result.getList());
		return m;
		
	}
	
	/**
	 * 建立指导老师与评阅老师关联关系
	 * @param juideId
	 * @param judgeId
	 * @return
	 */
	public boolean create_guideRelajudge(int guideId , int judgeId)
	{
		boolean success=false;
		Record relate=new Record();
		relate.set("guide_rela_judge_guide", guideId).set("guide_rela_judge_judge", judgeId);
		success=Db.save("guide_rela_judge", "guide_rela_judge_id", relate);
		return success;
	}
	
	/**
	 * 检查学生老师关联是否已存在
	 * @param stuId
	 * @param tchId
	 * @return
	 */
	public boolean checkRela_stu_tch(int stuId)
	{
		boolean sign=false;
		String sql="select * from student_rela_guideteacher where stu_rela_guidetch_stu="+stuId+" ";
		Record rec=Db.findFirst(sql);
		if(rec==null)
			sign=true;
		return sign;
	}
	
	/**
	 * 检查指导老师评阅老师关联是否已存在
	 * @param guideId
	 * @param judgeId
	 * @return
	 */
	public boolean checkRela_guide_judge(int guideId )
	{
		boolean sign=false;
		String sql="select * from guide_rela_judge where guide_rela_judge_guide="+guideId+" ";
		Record rec=Db.findFirst(sql);
		if(rec==null)
			sign=true;
		return sign;
	}
}
